package dto;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.List;

import model.Category;
import model.ProducerOwnCategory;
import model.Product;

public class DtoProduct {
	public static Product getProductWithProductId(DataManager dataManager,
			int productId) {
		Product product = null;

		Connection conn = dataManager.getConnection();
		Statement statement = null;
		ResultSet rs = null;
		if (conn != null) {
			try {
				statement = conn.createStatement();
				String sqlQuery = "select * from product where productId = "
						+ productId;

				rs = statement.executeQuery(sqlQuery);
				while (rs.next()) {
					product = new Product();

					product.setProductId(productId);
					product.setDateUpdated(rs.getDate(2));
					product.setProductName(rs.getString(3));
					product.setImage(rs.getString(4));
					product.setDescription(rs.getString(5));
					product.setPrice(rs.getInt(6));
					product.setQuantity(rs.getInt(7));
					product.setProducterOwnCategory(rs.getInt(8));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {

				try {
					if (rs != null)
						rs.close();
					if (statement != null)
						statement.close();
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return product;
	}

	private static List<Product> getAll(String sql, DataManager dataManager) {
		Product product = null;
		List<Product> products = new ArrayList<Product>();
		Connection conn = dataManager.getConnection();
		Statement statement = null;
		ResultSet rs = null;
		if (conn != null) {
			try {
				statement = conn.createStatement();

				rs = statement.executeQuery(sql);
				while (rs.next()) {
					product = new Product();

					product.setProductId(rs.getInt(1));
					product.setDateUpdated(rs.getDate(2));
					product.setProductName(rs.getString(3));
					product.setImage(rs.getString(4));
					product.setDescription(rs.getString(5));
					product.setPrice(rs.getInt(6));
					product.setQuantity(rs.getInt(7));
					product.setProducterOwnCategory(rs.getInt(8));
					products.add(product);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {

				try {
					if (rs != null)
						rs.close();
					if (statement != null)
						statement.close();
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return products;
	}

	public static List<Product> getLimitProduct(int start, int number,
			DataManager dataManager) {
		String sql = "select * from Product limit " + start + ", " + number;
		return getAll(sql, dataManager);
	}

	public static List<Product> getProductByProducerOwnCategory(
			List<ProducerOwnCategory> poc, DataManager dataManager) {
		List<Product> products = new ArrayList<Product>();
		for (ProducerOwnCategory p : poc) {
			String sql = "select * from Product where producerOwnCategoryId = "
					+ p.getProducerOwnCategoryId();
			products.addAll(getAll(sql, dataManager));
		}
		return products;
	}

	public static Product getById(int productId, DataManager dataManager) {
		String sql = "select * from Product where productId = " + productId;
		List<Product> products = getAll(sql, dataManager);

		if (products.isEmpty())
			return null;
		return products.get(0);
	}

	public static void getProductWithProducerOwnCategoryId(
			DataManager dataManager, int producerOwnCategoryId,
			List<Integer> productIds, List<String> productNames) {
		Connection conn = dataManager.getConnection();
		Statement statement = null;
		ResultSet rs = null;

		String sqlQuery = "select productId, productName from Product where producerOwnCategoryId = "
				+ producerOwnCategoryId;

		if (conn != null) {
			try {
				statement = conn.createStatement();

				rs = statement.executeQuery(sqlQuery);
				while (rs.next()) {
					productIds.add(rs.getInt(1));
					productNames.add(rs.getString(2));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (rs != null)
						rs.close();
					if (statement != null)
						statement.close();
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public static List<Product> getAllProduct(DataManager dataManager,
			List<String> categoryNames, List<String> producerNames) {
		String sqlQuery = "select Product.*,producerName,categoryName from Product,Producer,Category,ProducerOwnCategory "
				+ "where Product.producerOwnCategoryId = ProducerOwnCategory.producerOwnCategoryId "
				+ "and ProducerOwnCategory.producerId = Producer.producerId "
				+ "and ProducerOwnCategory.categoryId = Category.categoryId";

		List<Product> products = new ArrayList<Product>();
		Connection conn = dataManager.getConnection();
		Statement statement = null;
		ResultSet rs = null;
		if (conn != null) {
			try {
				statement = conn.createStatement();

				rs = statement.executeQuery(sqlQuery);
				while (rs.next()) {
					Product product = new Product();

					product.setProductId(rs.getInt(1));
					product.setDateUpdated(rs.getDate(2));
					product.setProductName(rs.getString(3));
					product.setImage(rs.getString(4));
					product.setDescription(rs.getString(5));
					product.setPrice(rs.getInt(6));
					product.setQuantity(rs.getInt(7));
					product.setProducterOwnCategory(rs.getInt(8));
					products.add(product);

					categoryNames.add(rs.getString(9));
					producerNames.add(rs.getString(10));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {

				try {
					if (rs != null)
						rs.close();
					if (statement != null)
						statement.close();
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return products;
	}

	public static List<Product> searchByProductName(String productName,
			DataManager dataManager) {
		String sqlQuery = "SELECT * FROM product WHERE productName LIKE '%"
				+ productName + "%'";
		return getAll(sqlQuery, dataManager);
	}

	private static void executeNonQuery(DataManager dataManager, String sqlQuery) {
		Connection conn = dataManager.getConnection();
		Statement statement = null;
		if (conn != null) {
			try {
				statement = conn.createStatement();
				statement.executeUpdate(sqlQuery);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {

				try {
					if (statement != null)
						statement.close();
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public static void updateProductWithProductId(DataManager dataManager,
			Product product) {
		Calendar cal = Calendar.getInstance();
		cal.setTime(product.getDateUpdated());
		String date = cal.get(Calendar.YEAR) + "-"
				+ (cal.get(Calendar.MONTH) + 1) + "-" + cal.get(Calendar.DATE);
		String sqlQuery = "update Product set productName = '"
				+ product.getProductName() + "', productImage = '"
				+ product.getImage() + "', dateUpdated = '" + date
				+ "', quantity = " + product.getQuantity() + ", price = "
				+ product.getPrice() + ", description = '"
				+ product.getDescription() + "', producerOwnCategoryId = "
				+ product.getProducterOwnCategory() + " where productId = "
				+ product.getProductId();

		executeNonQuery(dataManager, sqlQuery);
	}

	public static void addProduct(DataManager dataManager, Product product) {
		Calendar cal = Calendar.getInstance();
		cal.setTime(product.getDateUpdated());
		String date = cal.get(Calendar.YEAR) + "-"
				+ (cal.get(Calendar.MONTH) + 1) + "-" + cal.get(Calendar.DATE);
		String sqlQuery = "insert into Product (dateUpdated, productName, productImage, description, quantity, "
				+ "price, producerOwnCategoryId) values ('"
				+ date
				+ "', '"
				+ product.getProductName()
				+ "', '"
				+ product.getImage()
				+ "', '"
				+ product.getDescription()
				+ "', "
				+ product.getQuantity()
				+ ", "
				+ product.getPrice()
				+ ", "
				+ product.getProducterOwnCategory() + ")";

		executeNonQuery(dataManager, sqlQuery);
	}

	public static void deleteProduct(DataManager dataManager, int productId) {
		String sqlQuery = "delete from Product where productId = " + productId;
		executeNonQuery(dataManager, sqlQuery);
	}

	private static List<Product> getProductByCategoryId(DataManager dataManager,
			int categoryId) {

		String sql = "select product.* from product,category,producerowncategory " +
					"where category.categoryId = " + categoryId + " and producerowncategory.categoryId = category.categoryId " +
					"and product.producerOwnCategoryId = producerowncategory.producerOwnCategoryId " +
					"ORDER BY dateUpdated desc";
		
		return getAll(sql, dataManager);
	}
	
	private static LinkedList<Product> products;

	public static List<Product> getRecurisveProductWithCategoryId(int categoryId, DataManager dataManager, int n) {
		products = null;
		
		getRecurisveProductWithCategoryIdImpl(categoryId, n, dataManager);
		return products;
	}

	private static void getRecurisveProductWithCategoryIdImpl(int categoryId, int n, DataManager dataManager) {
		List<Category> categorys = DtoCategory.getByParentId(categoryId, dataManager);
		
		if (categorys.size() != 0) {
			for (Category category : categorys) {
				getRecurisveProductWithCategoryIdImpl(category.getCategoryId(), n, dataManager);
			}
		}
		
		List<Product> tempProducts = getProductByCategoryId(dataManager, categoryId);
		
		if (products == null && (tempProducts.size() != 0)) {
			products = new LinkedList<Product>();
			for (int i = 0; i < n && i < tempProducts.size(); ++i) 
				products.add(tempProducts.get(i));
		}
		
		else if (tempProducts.size() != 0) {
			int i, j;
			for (i = 0, j = 0; i < products.size()
					&& j < tempProducts.size(); ++i) {
				if (products.get(i).getDateUpdated().compareTo(tempProducts.get(j).getDateUpdated()) < 0) {
					products.add(i, tempProducts.get(j));
					++ j;
				}
			}
			if (i < n) {
				for (; i < n && j < tempProducts.size(); ++i, ++j)
					products.add(i, tempProducts.get(j));
			}
			for(i = 20; i < products.size();)
				products.removeLast();
		}
	}
}
